package com.example.carborrow.dao;

import com.example.carborrow.model.BRecord;
import com.example.carborrow.model.BorrowRecord;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;


@Repository
public class BorrowRecordDao {
    private final JdbcTemplate jdbcTemplate;


    @Autowired
    public BorrowRecordDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    private final  static String GET_ALL_RECS_SQL ="SELECT * FROM BorrowRecord";
    //获取所有记录
    public List<BorrowRecord> getAllBorrowRecords() {
        return jdbcTemplate.query(GET_ALL_RECS_SQL,new BorrowRecordRowMapper());
    }


    private final  static String GET_ALL_RECS_ORDER_SQL ="SELECT * FROM BorrowRecord ORDER BY state ASC, borrowDate DESC";
    //获取所有记录
    public List<BorrowRecord> getAllBorrowRecordsOrder() {
        return jdbcTemplate.query(GET_ALL_RECS_ORDER_SQL,new BorrowRecordRowMapper());
    }

    private final  static String GET_ALL_RECS_CUS_CAR_SQL ="select BorrowRecord.*,CarModel,name,phone from BorrowRecord,Car,Customer where BorrowRecord.customerId=Customer.customerId AND BorrowRecord.carId=Car.carId ORDER BY state ASC, borrowDate DESC";
    //获取所有记录拼接CustomerCar
    public List<BRecord> getAllBorrowRecordsCusCar() {
        return jdbcTemplate.query(GET_ALL_RECS_CUS_CAR_SQL,new BRecordRowMapper());
    }



    private final  static String GET_RECS_BY_STATUS_SQL ="SELECT * FROM BorrowRecord where state =?";
    //按状态查询记录
    public List<BorrowRecord> getBorrowRecordsByS(int status) {
        return jdbcTemplate.query(GET_RECS_BY_STATUS_SQL,new BorrowRecordRowMapper(),status);
    }

    private final  static String GET_RECS_BY_CUS_ID_SQL ="SELECT * FROM BorrowRecord where customerId =?";
    //按用户查询记录
    public List<BorrowRecord> getBorrowRecordsByCusId(int id) {
        return jdbcTemplate.query(GET_RECS_BY_CUS_ID_SQL,new BorrowRecordRowMapper(),id);
    }

    private final  static String GET_RECS_BY_CUS_ID_ORDER_SQL ="SELECT * FROM BorrowRecord where customerId =? ORDER BY state ASC, borrowDate DESC";
    //按用户查询记录 排序
    public List<BorrowRecord> getBorrowRecordsByCusIdOrder(int id) {
        return jdbcTemplate.query(GET_RECS_BY_CUS_ID_ORDER_SQL,new BorrowRecordRowMapper(),id);
    }

    private final  static String GET_RECS_BY_CUS_ID_STATE_SQL ="SELECT * FROM BorrowRecord where customerId =? and state =?";
    //按 用户 和 归还状态 查询记录
    public List<BorrowRecord> getBorrowRecordsByCusIdState(int cusId,int state){
        return jdbcTemplate.query(GET_RECS_BY_CUS_ID_STATE_SQL,new BorrowRecordRowMapper(),cusId,state);
    }


    private final static String SEARCH_RED_BY_CUS_ID_SQL = "SELECT * FROM BorrowRecord WHERE customerId = ? and (borrowDate like ? or returnDate like ? ) ORDER BY state ASC, borrowDate DESC";
    //模糊查询 我的 记录 排序
    public List<BorrowRecord> searchBorrowRecordOrder(String searchWord,int cusId) {
        String sw = "%" + searchWord + "%";
        return jdbcTemplate.query(SEARCH_RED_BY_CUS_ID_SQL, new BorrowRecordRowMapper(), cusId,sw,sw);

    }



    private final static String GET_RED_BY_BORDATE = "SELECT * FROM BorrowRecord WHERE borrowDate = ?";
    //按出借日期查询记录
    public BorrowRecord getBorrowRecordByBDATE(Date bdate) {

        return jdbcTemplate.queryForObject(GET_RED_BY_BORDATE, new BorrowRecordRowMapper(), bdate);
    }






    //通过recordId 获得 carId
    private final static String GET_CAR_ID_BY_RECORD_ID_SQL = "select carId from BorrowRecord where recordId=?";
    public int getCarIdByRecordId(int recordId) {
        try {
            return jdbcTemplate.queryForObject(GET_CAR_ID_BY_RECORD_ID_SQL, Integer.class, recordId);
        } catch (Exception e) {
            return 0;
        }
    }

    private final static String ADD_REC_SQL = "INSERT INTO BorrowRecord (carId,customerId, borrowDate, returnDate) VALUES (?, ?, ?, ?)";

    // 新增记录  插入时String自动->Date
    public int addBorrowRecord(int carId, int customerId ,String borrowDate , String returnDate) {
        return jdbcTemplate.update(ADD_REC_SQL, carId,customerId, borrowDate, returnDate);
    }



    private final static String UPDATE_REC_SQL ="UPDATE BorrowRecord SET carId=?, customerId = ?, borrowDate = ?, returnDate = ?, state = ? WHERE recordId = ?";

    // 更新记录信息
    public int updateBorrowRecord(BorrowRecord brecord) {
        return jdbcTemplate.update(UPDATE_REC_SQL, brecord.getCarId(), brecord.getCustomerId(), brecord.getBorrowDate(), brecord.getReturnDate(), brecord.getState(), brecord.getBorrowRecordId());
    }

    //更新记录状态
    private final static String UPDATE_REC_STATE_SQL ="UPDATE BorrowRecord SET state = ? WHERE recordId = ?";
    public int updateBorrowRecordState(int state,int recordId) {
        return jdbcTemplate.update(UPDATE_REC_STATE_SQL, state,recordId);
    }

    private final static String DEL_REC_SQL="DELETE FROM BorrowRecord WHERE recordId = ?";
    // 删除记录
    public int deleteBorrowRecord(int brecordId) {
         return jdbcTemplate.update(DEL_REC_SQL, brecordId);
    }


    private static class BorrowRecordRowMapper implements RowMapper<BorrowRecord> {
        @Override
        public BorrowRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
            BorrowRecord brecord = new BorrowRecord();
            brecord.setBorrowRecordId(rs.getInt("recordId"));
            brecord.setCarId(rs.getInt("carId"));
            brecord.setCustomerId(rs.getInt("customerId"));
            brecord.setBorrowDate(rs.getDate("borrowDate"));
            brecord.setReturnDate(rs.getDate("returnDate"));
            brecord.setState(rs.getInt("state"));
            return brecord;
        }
    }
    private static class BRecordRowMapper implements RowMapper<BRecord> {
        @Override
        public BRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
            BRecord brecord = new BRecord();
            brecord.setBorrowRecordId(rs.getInt("recordId"));
            brecord.setCarId(rs.getInt("carId"));
            brecord.setCustomerId(rs.getInt("customerId"));
            brecord.setBorrowDate(rs.getDate("borrowDate"));
            brecord.setReturnDate(rs.getDate("returnDate"));
            brecord.setState(rs.getInt("state"));
            brecord.setCarModel(rs.getString("carModel"));
            brecord.setName(rs.getString("name"));
            brecord.setPhone(rs.getString("phone"));
            return brecord;
        }
    }
    
    
    
}
